{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Using Amazon Redshift Spectrum to Access Data in S3\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can leverage our previously created table in Amazon Athena with its metadata and schema information stored in the AWS Glue Data Catalog to access our data in S3 through Redshift Spectrum. All we need to do is create an external schema in Redshift, point it to our AWS Glue Data Catalog, and point Redshift to the database we’ve created.  \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import boto3\n",
    "import sagemaker\n",
    "\n",
    "# Get region \n",
    "session = boto3.session.Session()\n",
    "region_name = session.region_name\n",
    "\n",
    "# Connect to Redshift\n",
    "redshift = boto3.client('redshift')\n",
    "secretsmanager = boto3.client('secretsmanager')\n",
    "\n",
    "# Get SageMaker session & default S3 bucket\n",
    "sagemaker_session = sagemaker.Session()\n",
    "bucket = sagemaker_session.default_bucket()\n",
    "\n",
    "# Set S3 prefixes\n",
    "tsv_prefix = 'amazon-reviews-pds/tsv'\n",
    "\n",
    "# Set S3 path to TSV data\n",
    "s3_path_tsv = 's3://{}/{}'.format(bucket, tsv_prefix)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Setup Redshift Connection Via SQLAlchemy\n",
    "The Python SQL Toolkit and Object Relational Mapper (https://pypi.org/project/SQLAlchemy/)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "!pip install -q SQLAlchemy==1.3.13"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine\n",
    "from sqlalchemy.orm import sessionmaker\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Get Redshift credentials"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "\n",
    "secret = secretsmanager.get_secret_value(SecretId='dsoaws_redshift_login')\n",
    "cred = json.loads(secret['SecretString'])\n",
    "\n",
    "master_user_name = cred[0]['username']\n",
    "master_user_pw = cred[1]['password']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Redshift configuration parameters"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "redshift_cluster_identifier = 'dsoaws'\n",
    "\n",
    "database_name_redshift = 'dsoaws'\n",
    "database_name_athena = 'dsoaws'\n",
    "\n",
    "redshift_port = '5439'\n",
    "\n",
    "schema_redshift = 'redshift'\n",
    "schema_athena = 'athena'\n",
    "\n",
    "table_name_tsv = 'amazon_reviews_tsv'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Get Redshift endpoint address & IAM Role"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "response = redshift.describe_clusters(ClusterIdentifier=redshift_cluster_identifier)\n",
    "\n",
    "redshift_endpoint_address = response['Clusters'][0]['Endpoint']['Address']\n",
    "iam_role = response['Clusters'][0]['IamRoles'][0]['IamRoleArn']\n",
    "\n",
    "print(redshift_endpoint_address)\n",
    "print(iam_role)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Connect to Redshift database engine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "engine = create_engine('postgresql://{}:{}@{}:{}/{}'.format(master_user_name, master_user_pw, redshift_endpoint_address, redshift_port, database_name_redshift))\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Configure Session"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "session = sessionmaker()\n",
    "session.configure(bind=engine)\n",
    "s = session()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Redshift Spectrum\n",
    "Amazon Redshift Spectrum directly queries data in S3, using the same SQL syntax of Amazon Redshift. You can also run queries that span both the frequently accessed data stored locally in Amazon Redshift and your full datasets stored cost-effectively in S3.\n",
    "\n",
    "To use Redshift Spectrum, your cluster needs authorization to access data catalog in Amazon Athena and your data files in Amazon S3. You provide that authorization by referencing an AWS Identity and Access Management (IAM) role that is attached to your cluster. \n",
    "\n",
    "To use this capability in from your Amazon SageMaker notebook:\n",
    "\n",
    "* Register your Athena database `dsoaws` with Redshift Spectrum\n",
    "* Query Your Data in Amazon S3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Register Athena Database `dsoaws` with Redshift Spectrum to access the data directly in S3 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "statement = \"\"\"\n",
    "CREATE EXTERNAL SCHEMA IF NOT EXISTS {} FROM DATA CATALOG \n",
    "    DATABASE '{}' \n",
    "    IAM_ROLE '{}'\n",
    "    REGION '{}'\n",
    "    CREATE EXTERNAL DATABASE IF NOT EXISTS\n",
    "\"\"\".format(schema_athena, database_name_athena, iam_role, region_name)\n",
    "\n",
    "print(statement)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "s.execute(statement)\n",
    "s.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Congratulations\n",
    "\n",
    "So with just one command, we now have access and can query our S3 data lake from Amazon Redshift without moving any data into our data warehouse. This is the power of Redshift Spectrum. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Run a sample query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "statement = \"\"\"\n",
    "SELECT product_category, COUNT(star_rating) AS count_star_rating\n",
    "    FROM {}.{}\n",
    "    GROUP BY product_category\n",
    "    ORDER BY count_star_rating DESC\n",
    "\"\"\".format(schema_athena, table_name_tsv)\n",
    "\n",
    "print(statement)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_sql_query(statement, engine)\n",
    "df.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### But now, let’s actually copy some data from S3 into Amazon Redshift. Let’s pull in customer reviews data from the years 2014 and 2015. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load TSV Data Into Redshift\n",
    "\n",
    "Create local Redshift tables with Customer Reviews data of each year for the last 2 years"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img src=\"img/c3-10.png\" width=\"90%\" align=\"left\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create `redshift` schema"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "statement = \"\"\"CREATE SCHEMA IF NOT EXISTS {}\"\"\".format(schema_redshift)\n",
    "\n",
    "s = session()\n",
    "s.execute(statement)\n",
    "s.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create Redshift tables for every year"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "When you create a table, you can specify one or more columns as the **sort key**. Amazon Redshift stores your data on disk in sorted order according to the sort key. This means, you can optimize your table by choosing a sort key that reflects your most frequently used query types. If you query a lot of recent data, you can specify a timestamp column as the sort key. If you frequently query based on range or equality filtering on one column, you should choose that column as the sort key. \n",
    "\n",
    "As we are going to run a lot of queries in the next chapter filtering on `product_category`, let’s choose that one as our sort key. \n",
    "\n",
    "You can also define a distribution style for every table. When you load data into a table, Redshift distributes the rows of the table among your cluster nodes according to the table’s distribution style. When you run a query, the query optimizer redistributes the rows to the cluster nodes as needed to perform any joins and aggregations. So our goal should be to optimize the rows distribution to minimize needed data movements. There are three distribution styles from which you can choose from: \n",
    "\n",
    "KEY distribution - distribute the rows according to the values in one column\n",
    "ALL distribution - distribute a copy of the entire table to every node\n",
    "EVEN distribution - the rows are distributed across all nodes in a round-robin-fashion which is the default distribution style\n",
    "\n",
    "For our table, we’ve chosen **KEY distribution** based on `product_id` as this column has a high cardinality, shows an even distribution and can be used to join with other tables. \n",
    "\n",
    "Now we are ready to copy the data from S3 into our new Redshift table. \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create table function, pass session, table name prefix and start & end year\n",
    "\n",
    "def create_redshift_table_tsv(session, table_name_prefix, start_year, end_year):\n",
    "    for year in range(start_year, end_year + 1, 1):\n",
    "        current_table_name = table_name_prefix+'_'+str(year)\n",
    "        statement = \"\"\"\n",
    "        CREATE TABLE IF NOT EXISTS redshift.{}( \n",
    "             marketplace varchar(2) ENCODE zstd,\n",
    "             customer_id varchar(8) ENCODE zstd,\n",
    "             review_id varchar(14) ENCODE zstd,\n",
    "             product_id varchar(10) ENCODE zstd DISTKEY,\n",
    "             product_parent varchar(10) ENCODE zstd,\n",
    "             product_title varchar(400) ENCODE zstd,\n",
    "             product_category varchar(24) ENCODE raw,\n",
    "             star_rating int ENCODE az64,\n",
    "             helpful_votes int ENCODE zstd,\n",
    "             total_votes int ENCODE zstd,\n",
    "             vine varchar(1) ENCODE zstd,\n",
    "             verified_purchase varchar(1) ENCODE zstd,\n",
    "             review_headline varchar(128) ENCODE zstd,\n",
    "             review_body varchar(65535) ENCODE zstd,\n",
    "             review_date varchar(10) ENCODE bytedict,\n",
    "             year int ENCODE az64)  SORTKEY (product_category)\n",
    "        \"\"\".format(current_table_name)\n",
    "\n",
    "        #print(statement)\n",
    "        session.execute(statement)\n",
    "    session.commit()\n",
    "        \n",
    "    print(\"Done.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "create_redshift_table_tsv(s, 'amazon_reviews_tsv', 2014, 2015)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Insert Data from Athena table into local Redshift table\n",
    "\n",
    "For such bulk inserts, you can either use a `COPY` command, or an `INSERT INTO` command. In general, the `COPY` command is preferred, as it loads data in parallel and more efficiently from Amazon S3, or other supported data sources. \n",
    "\n",
    "If you are loading data or a subset of data from one table into another, you can use the `INSERT INTO` command with a `SELECT` clause for high-performance data insertion. As we’re loading our data from the `athena.amazon_reviews_tsv` table, let’s choose this option. \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# INSERT INTO function, pass session, table name prefix and start & end year\n",
    "\n",
    "def insert_into_redshift_table_tsv(session, table_name_prefix, start_year, end_year):\n",
    "    for year in range(start_year, end_year + 1, 1):\n",
    "        print(year)\n",
    "        current_table_name = table_name_prefix+'_'+str(year)\n",
    "        statement = \"\"\"\n",
    "            INSERT \n",
    "            INTO\n",
    "                redshift.{}\n",
    "                SELECT\n",
    "                    marketplace,\n",
    "                    customer_id,\n",
    "                    review_id,\n",
    "                    product_id,\n",
    "                    product_parent,\n",
    "                    product_title,\n",
    "                    product_category,\n",
    "                    star_rating,\n",
    "                    helpful_votes,\n",
    "                    total_votes,\n",
    "                    vine,\n",
    "                    verified_purchase,\n",
    "                    review_headline,\n",
    "                    review_body,\n",
    "                    review_date,\n",
    "                    CAST(DATE_PART_YEAR(TO_DATE(review_date, 'YYYY-MM-DD')) AS INTEGER) AS year\n",
    "                FROM\n",
    "                    athena.amazon_reviews_tsv             \n",
    "                WHERE\n",
    "                    year = {}\n",
    "            \"\"\".format(current_table_name, year)\n",
    "        #print(statement)\n",
    "        session.execute(statement)\n",
    "        session.commit()        \n",
    "    print(\"Done.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Note: The `INSERT INTO` takes approx. 10min/table, please be patient and minimize the no. of tables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "insert_into_redshift_table_tsv(s, 'amazon_reviews_tsv', 2014, 2015)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You might notice that we use a date conversion to parse the year out of our `review_date` column and store it in a separate `year` column which we then use to filter on all records from 2015. This is an example on how you can simplify ETL tasks, as we’re putting our data transformation logic directly in a `SELECT` query and ingest the result into Redshift. \n",
    "\n",
    "Another way to optimize our tables would be to create them as a sequence of time-series tables, especially when our data has a fixed retention period. Let’s say we want to store data of the last 2 years (24 months) in our data warehouse, and update with new data once a month. \n",
    "\n",
    "If you create one table per month, you can easily remove old data simply by running a `DROP TABLE` command on the corresponding table. This approach is much faster than running a large-scale DELETE process and also saves you from having to run a subsequent VACUUM process to reclaim space and re-sort the rows. \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Query Redshift\n",
    "\n",
    "To combine query results across tables, we can use a `UNION ALL` view. Similarly, when we need to delete old data, we remove the dropped table from the UNION ALL view. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Use `UNION ALL` across 2 tables (2015, 2014) in our `redshift` schema"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "statement = \"\"\"\n",
    "SELECT year, product_category, COUNT(star_rating) AS count_star_rating\n",
    "  FROM redshift.amazon_reviews_tsv_2015\n",
    "  GROUP BY redshift.amazon_reviews_tsv_2015.product_category, year\n",
    "UNION ALL\n",
    "SELECT year, product_category, COUNT(star_rating) AS count_star_rating\n",
    "  FROM redshift.amazon_reviews_tsv_2014\n",
    "  GROUP BY redshift.amazon_reviews_tsv_2014.product_category, year\n",
    "ORDER BY product_category ASC, year DESC\n",
    "\"\"\"\n",
    "\n",
    "print(statement)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_sql_query(statement, engine)\n",
    "df.head(20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Run the same query on our orginal data in S3 / the `athena` schema to verify the results match"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "statement = \"\"\"\n",
    "SELECT CAST(DATE_PART_YEAR(TO_DATE(review_date, 'YYYY-MM-DD')) AS INTEGER) AS year, product_category, COUNT(star_rating) AS count_star_rating\n",
    "  FROM athena.amazon_reviews_tsv\n",
    "  WHERE year = 2015 OR year = 2014 \n",
    "  GROUP BY athena.amazon_reviews_tsv.product_category, year\n",
    "ORDER BY product_category ASC, year DESC\n",
    "\"\"\"\n",
    "\n",
    "print(statement)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_sql_query(statement, engine)\n",
    "df.head(20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Now Query Across Both Redshift and Athena in a single query\n",
    "\n",
    "Use `UNION ALL` across 2 Redshift tables (2015, 2014) and the rest from Athena/S3 (2013-1995)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "statement = \"\"\"\n",
    "SELECT year, product_category, COUNT(star_rating) AS count_star_rating\n",
    "  FROM redshift.amazon_reviews_tsv_2015\n",
    "  GROUP BY redshift.amazon_reviews_tsv_2015.product_category, year\n",
    "UNION ALL\n",
    "SELECT year, product_category, COUNT(star_rating) AS count_star_rating\n",
    "  FROM redshift.amazon_reviews_tsv_2014\n",
    "  GROUP BY redshift.amazon_reviews_tsv_2014.product_category, year\n",
    "UNION ALL\n",
    "SELECT CAST(DATE_PART_YEAR(TO_DATE(review_date, 'YYYY-MM-DD')) AS INTEGER) AS year, product_category, COUNT(star_rating) AS count_star_rating\n",
    "  FROM athena.amazon_reviews_tsv\n",
    "  WHERE year <= 2013\n",
    "  GROUP BY athena.amazon_reviews_tsv.product_category, year\n",
    "ORDER BY product_category ASC, year DESC\n",
    "\"\"\"\n",
    "\n",
    "print(statement)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_sql_query(statement, engine)\n",
    "df.head(20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Let's verify that both Redshift tables and S3 tables got queried by running  `Explain`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "statement = \"\"\"\n",
    "EXPLAIN SELECT year, product_category, COUNT(star_rating) AS count_star_rating\n",
    "  FROM redshift.amazon_reviews_tsv_2015\n",
    "  GROUP BY redshift.amazon_reviews_tsv_2015.product_category, year\n",
    "UNION ALL\n",
    "SELECT year, product_category, COUNT(star_rating) AS count_star_rating\n",
    "  FROM redshift.amazon_reviews_tsv_2014\n",
    "  GROUP BY redshift.amazon_reviews_tsv_2014.product_category, year\n",
    "UNION ALL\n",
    "SELECT CAST(DATE_PART_YEAR(TO_DATE(review_date, 'YYYY-MM-DD')) AS INTEGER) AS year, product_category, COUNT(star_rating) AS count_star_rating\n",
    "  FROM athena.amazon_reviews_tsv\n",
    "  WHERE year <= 2013\n",
    "  GROUP BY athena.amazon_reviews_tsv.product_category, year\n",
    "ORDER BY product_category ASC, year DESC\n",
    "\"\"\"\n",
    "\n",
    "print(statement)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "pd.set_option('display.max_rows', None)\n",
    "pd.set_option('display.max_columns', None)\n",
    "pd.set_option('display.width', None)\n",
    "pd.set_option('display.max_colwidth', -1)\n",
    "\n",
    "df = pd.read_sql_query(statement, engine)\n",
    "df.head(100)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Expected Output"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "QUERYPLAN\n",
    "XN Merge  (cost=1000177373551.14..1000177373584.69 rows=13420 width=1040)\n",
    "  Merge Key: product_category, year\n",
    "  ->  XN Network  (cost=1000177373551.14..1000177373584.69 rows=13420 width=1040)\n",
    "        Send to leader\n",
    "        ->  XN Sort  (cost=1000177373551.14..1000177373584.69 rows=13420 width=1040)\n",
    "              Sort Key: product_category, year\n",
    "              ->  XN Append  (cost=733371.52..177372631.06 rows=13420 width=1040)\n",
    "                    ->  XN Subquery Scan *SELECT* 1  (cost=733371.52..733372.06 rows=43 width=22)\n",
    "                          ->  XN HashAggregate  (cost=733371.52..733371.63 rows=43 width=22)\n",
    "                                ->  XN Seq Scan on amazon_reviews_tsv_2015  (cost=0.00..419069.44 rows=41906944 width=22)\n",
    "                    ->  XN Subquery Scan *SELECT* 2  (cost=772258.45..772258.98 rows=43 width=23)\n",
    "                          ->  XN HashAggregate  (cost=772258.45..772258.55 rows=43 width=23)\n",
    "                                ->  XN Seq Scan on amazon_reviews_tsv_2014  (cost=0.00..441290.54 rows=44129054 width=23)\n",
    "                    ->  XN Subquery Scan *SELECT* 3  (cost=175866766.67..175867000.02 rows=13334 width=1040)\n",
    "                          ->  XN HashAggregate  (cost=175866766.67..175866866.68 rows=13334 width=1040)\n",
    "                                ->  XN S3 Query Scan amazon_reviews_tsv  (cost=175000000.00..175766766.67 rows=13333334 width=1040)\n",
    "                                      Filter: (date_part_year(to_date((derived_col1)::text, 'YYYY-MM-DD'::text)) <= 2013)\n",
    "                                      ->  S3 HashAggregate  (cost=175000000.00..175000100.00 rows=40000000 width=1036)\n",
    "                                            ->  S3 Seq Scan athena.amazon_reviews_tsv location:s3://sagemaker-us-west-2-237178646982/amazon-reviews-pds/tsv format:TEXT  (cost=0.00..100000000.00 rows=10000000000 width=1036)\n",
    "----- Tables missing statistics: amazon_reviews_tsv_2015, amazon_reviews_tsv_2014 -----\n",
    "----- Update statistics by running the ANALYZE command on these tables -----\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## When to use Athena, and when to use Redshift?\n",
    "\n",
    "**Amazon Athena** should be your preferred choice when running ad-hoc SQL queries on data that is stored in Amazon S3. It doesn’t require you to set up or manage any infrastructure resources, and you don’t need to move any data. It supports structured, unstructured, and semi-structured data. With Athena, you are defining a “schema on read” - you basically just log in, create a table and you are good to go. \n",
    "\n",
    "**Amazon Redshift** is targeted for modern data analytics on large sets of structured data. Here, you need to have a predefined “schema on write”. Unlike serverless Athena, Redshift requires you to create a cluster (compute and storage resources), ingest the data and build tables before you can start to query, but caters to performance and scale. So for any highly-relational data with a transactional nature (data gets updated), workloads which involve complex joins, and latency requirements to be sub-second, Redshift is the right choice.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "conda_python3",
   "language": "python",
   "name": "conda_python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
